﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.Common;

namespace CanAmFramework.CopyData
{
    class Program
    {
        private static string buildSqlConnectionString(string instance, string database, string userId, string password)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = instance;
            builder.InitialCatalog = database;
            if (string.IsNullOrEmpty(userId))
            {
                builder.IntegratedSecurity = true;
            }
            else
            {
                builder.IntegratedSecurity = false;
                builder.UserID = userId;
                builder.Password = password;
            }
            return builder.ConnectionString;
        }

        private static string buildOracleConnectionString(string database, string userId, string password)
        {
            OracleConnectionStringBuilder builder = new OracleConnectionStringBuilder();
            builder.DataSource = database;
            builder.UserID = userId;
            builder.Password = password;
            return builder.ConnectionString;
        }

        class ColumnInfo
        {
            public string ColumnName { get; set; }
            public string ParameterName { get; set; }
            public SqlParameter Parameter { get; set; }
            public int SourceColumnNumber { get; set; }
        }

        static void writeOutput(string message, params object[] args)
        {
            Console.WriteLine(DateTime.Now.ToString() + ": " + string.Format(message, args));
        }

        static int Main(string[] args)
        {
            try
            {
                ArgumentParser parser = new ArgumentParser(args);

                string toInstanceName = parser.GetArgumentValue("tosqlinstance", true);
                string toDatabaseName = parser.GetArgumentValue("tosqldatabase", true);
                string toUserId = parser.GetArgumentValue("touserid", false);
                string toPassword = parser.GetArgumentValue("topassword", false);
                string toTableName = parser.GetArgumentValue("totable", true);

                string fromTableName = parser.GetArgumentValue("fromtable", true);
                string fromWhereClause = parser.GetArgumentValue("fromwhereclause", false);
                string columnList = parser.GetArgumentValue("columns", true);
                var columns = (
                    from c in columnList.Split(',')
                    orderby c
                    select new ColumnInfo { ColumnName = c.Trim().ToLower(), ParameterName = "@" + c.Trim().ToLower() }).ToList();

                string delimitedColumnList = string.Join(", ", columns.Select(c => c.ColumnName).ToArray());
                string delimitedParameterList = string.Join(", ", columns.Select(c => c.ParameterName).ToArray());

                string fromOracleDatabase = parser.GetArgumentValue("fromoracledatabase", false);
                string fromUserId = parser.GetArgumentValue("fromuserid", false);
                string fromPassword = parser.GetArgumentValue("frompassword", false);

                string fromSqlInstance = parser.GetArgumentValue("fromsqlinstance", false);
                string fromSqlDatabase = parser.GetArgumentValue("fromsqldatabase", false);

                //TODO: validate arguments

                int count = 0;

                SqlCommand insertCommand = null;

                // Open connection to SQL Server
                using (var sqlConnection = new SqlConnection(buildSqlConnectionString(toInstanceName, toDatabaseName, toUserId, toPassword)))
                {
                    writeOutput("Connecting to target database {0}:{1}...", toInstanceName, toDatabaseName);

                    sqlConnection.Open();

                    // Create connection to source database
                    DbConnection fromConnection = null;
                    if (!string.IsNullOrEmpty(fromOracleDatabase))
                    {
                        fromConnection = new OracleConnection(buildOracleConnectionString(fromOracleDatabase, fromUserId, fromPassword));
                    }
                    else
                    {
                        fromConnection = new SqlConnection(buildSqlConnectionString(fromSqlInstance, fromSqlDatabase, fromUserId, fromPassword));
                    }

                    // Query source database for data, pushing records row by row to SQL Server
                    using (fromConnection)
                    {
                        writeOutput("Connecting to source database {0}...", fromOracleDatabase ?? fromSqlInstance + ":" + fromSqlDatabase);
                        fromConnection.Open();

                        writeOutput("Truncating table {0}...", toTableName);
                        // Truncate SQL table
                        using (var command = sqlConnection.CreateCommand())
                        {
                            command.CommandText = "truncate table " + toTableName;
                            command.ExecuteNonQuery();
                        }

                        writeOutput("Loading SQL Server table {0} from source table {1}...", toTableName, fromTableName);

                        using (var selectCommand = fromConnection.CreateCommand())
                        {
                            selectCommand.CommandText = String.Format("select {0} from {1}", delimitedColumnList, fromTableName);
                            if (!string.IsNullOrEmpty(fromWhereClause))
                                selectCommand.CommandText = selectCommand.CommandText + " where " + fromWhereClause;

                            using (var reader = selectCommand.ExecuteReader())
                            {
                                // Open SQL Server transaction
                                SqlTransaction insertTransaction = sqlConnection.BeginTransaction();

                                while (reader.Read())
                                {
                                    // Insert into SQL Server
                                    if (insertCommand == null)
                                    {
                                        insertCommand = sqlConnection.CreateCommand();
                                        insertCommand.CommandText = String.Format("insert into {0} ({1}) values ({2})", toTableName, delimitedColumnList, delimitedParameterList);
                                        foreach (var column in columns)
                                        {
                                            column.SourceColumnNumber = reader.GetOrdinal(column.ColumnName);
                                            column.Parameter = insertCommand.Parameters.AddWithValue(column.ParameterName, reader.GetValue(column.SourceColumnNumber));
                                        }
                                        insertCommand.Transaction = insertTransaction;
                                    }
                                    else
                                    {
                                        foreach (var column in columns)
                                        {
                                            column.Parameter.Value = reader.GetValue(column.SourceColumnNumber);
                                        }
                                    }

                                    insertCommand.ExecuteNonQuery();
                                    count++;
                                    if (count % 250 == 0)
                                    {
                                        writeOutput("{0} records loaded.", count);
                                    }
                                    if (count % 2500 == 0)
                                    {
                                        writeOutput("{0} records loaded. Committing...", count);
                                        insertTransaction.Commit();
                                        insertTransaction = sqlConnection.BeginTransaction();
                                        insertCommand.Transaction = insertTransaction;
                                    }
                                }
                                insertTransaction.Commit();
                            }
                        }
                    }
                }

                writeOutput("{0} records loaded.", count);
                writeOutput("Load complete.");
                return 0;
            }
            catch (Exception ex)
            {
                writeOutput(ex.ToString());
                return 1;
            }
        }
    }
}
